import pandas as pd
from flask import Flask
from flask_mysqldb import MySQL


def insert_data_to_db(df):
    # 创建一个 Flask 应用实例
    app = Flask(__name__)

    # 配置 MySQL 数据库
    app.config['MYSQL_HOST'] = '106.14.137.132'
    app.config['MYSQL_USER'] = 'root'
    app.config['MYSQL_PASSWORD'] = 'zx20040714'
    app.config['MYSQL_DB'] = 'bsproject'

    # 初始化 MySQL
    mysql = MySQL(app)

    # 使用应用上下文
    with app.app_context():
        # 获取数据库连接
        conn = mysql.connection
        cursor = conn.cursor()

        # SQL 插入语句 (不包含 transaction_id，因为它是 auto_increment)
        insert_query = """
        INSERT INTO transactions (
            transaction_id, user_id, transaction_amount, transaction_type, transaction_timestamp, 
            account_balance, device_type, location, merchant_category, 
            ip_address_flag, previous_fraudulent_activity, daily_transaction_count, 
            avg_transaction_amount_7d, failed_transaction_count_7d, card_type, 
            card_age, transaction_distance, authentication_method, risk_score, 
            is_weekend, fraud_label
        ) VALUES (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s, %s, %s);
        """

        # 确保 transaction_timestamp 是正确的时间格式
        df['transaction_timestamp'] = pd.to_datetime(df['transaction_timestamp'], errors='coerce')

        # 处理缺失值：将 NaN 替换为 None，或使用 fillna() 处理
        # df = df.fillna({
        #     'user_id': 0,  # 假设缺失的 user_id 用 0 填充
        #     'transaction_amount': 0.0,
        #     'transaction_type': 'Unknown',  # 用 Unknown 填充缺失类型
        #     'account_balance': 0.0,
        #     'device_type': 'Unknown',
        #     'location': 'Unknown',
        #     'merchant_category': 'Unknown',
        #     'ip_address_flag': 0,
        #     'previous_fraudulent_activity': 0,
        #     'daily_transaction_count': 0,
        #     'avg_transaction_amount_7d': 0.0,
        #     'failed_transaction_count_7d': 0,
        #     'card_type': 'Unknown',
        #     'card_age': 0,
        #     'transaction_distance': 0.0,
        #     'authentication_method': 'Unknown',
        #     'risk_score': 0.0,
        #     'is_weekend': 0,
        #     'fraud_label': 0
        # })

        # 将 DataFrame 中的数据逐行插入数据库
        for index, row in df.iterrows():
            # 确保 transaction_timestamp 是字符串格式
            timestamp_str = row['transaction_timestamp'].strftime('%Y-%m-%d %H:%M:%S')

            # 打印调试信息：插入的参数和查询
            print(f"Inserting row {index}: {row.to_dict()}")  # 打印插入数据的行
            print(f"SQL Query: {insert_query}")  # 打印 SQL 查询
            print(row)
            # 执行插入
            cursor.execute(insert_query, (
                row['transaction_id'],
                row['user_id'],
                row['transaction_amount'],
                row['transaction_type'],
                timestamp_str,  # 将时间格式化为字符串
                row['account_balance'],
                row['device_type'],
                row['location'],
                row['merchant_category'],
                row['ip_address_flag'],
                row['previous_fraudulent_activity'],
                row['daily_transaction_count'],
                row['avg_transaction_amount_7d'],
                row['failed_transaction_count_7d'],
                row['card_type'],
                row['card_age'],
                row['transaction_distance'],
                row['authentication_method'],
                row['risk_score'],
                row['is_weekend'],
                row['fraud_label']
            ))

        # 提交事务并关闭连接
        conn.commit()
        cursor.close()
        # conn.close()


def main():
    # 读取 CSV 数据
    data_path = 'synthetic_fraud_dataset.csv'  # 你的文件名
    df = pd.read_csv(data_path)


    # 确保所有字段名小写
    df.columns = df.columns.str.lower()


    # 调用插入数据函数
    insert_data_to_db(df)


if __name__ == '__main__':
    main()
